package com.zzx.dao.imql;

import com.zzx.Util.DruidUtil;
import com.zzx.bean.Courier;
import com.zzx.bean.User;
import com.zzx.dao.BaseUserDao;
import com.zzx.exception.DuplicateIdentityException;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: Spider Man
 * @Description:
 * @Date Created in 2020-09-16 9:53
 * @Modified By:
 */
public class UserDaoImp implements BaseUserDao {

    private final String SQL_CONSOLE = "SELECT COUNT(uid) size,COUNT(TO_DAYS(uregistertime) =TO_DAYS(NOW()) OR NULL) day FROM user";
    private final String SQL_FIND_ALL_USER_INFO = "SELECT * FROM user";
    private final String SQL_FIND_ALL_USER_INFO_LIMIT = "SELECT * FROM user LIMIT ?,?";
    private final String SQL_FIND_USER_BY_U_PHONE = "SELECT * FROM user WHERE uPhone=?;";
    private final String SQL_INSERT = "INSERT user (uName,uPhone,uIdentity,uPassword,uRegisterTime) VALUES(?,?,?,?,NOW())";
    private final String SQL_UPDATE = "UPDATE user SET uName=?,uPhone=?,uIdentity=?,uPassword=? WHERE uid=?";
    private final String SQL_DELETE = "DELETE from user where uid=?";

    /**
     * 用于显示用户人数和用户日注册量
     *
     * @return {size:总数，day:日增加量}
     */
    @Override
    public List<Map<String, Integer>> console() {
        List<Map<String, Integer>> list = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_CONSOLE);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                int size = resultSet.getInt("size");
                int day = resultSet.getInt("day");
                Map<String, Integer> map = new HashMap<>();
                map.put("size",size);
                map.put("day",day);
                list.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return list;
    }

    /**
     * 用于查询所有用户的信息
     *
     * @param limit      是否分页的标记，true表示要分页，false表示不用分页
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 用户员的集合
     */
    @Override
    public List<User> findAllUserInfo(boolean limit, int offset, int pageNumber) {
        List<User> list = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            assert connection != null;
            if (limit) {
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL_USER_INFO_LIMIT);
                // 3.参数填充
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, pageNumber);
            } else {
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL_USER_INFO);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Integer cid = resultSet.getInt("uid");
                String cName = resultSet.getString("uName");
                String cPhone = resultSet.getString("uPhone");
                String cIdentity = resultSet.getString("uIdentity");
                String cPassword = resultSet.getString("uPassword");
                Timestamp cRegisterTime = resultSet.getTimestamp("uRegisterTime");
                Timestamp cLoginTime = resultSet.getTimestamp("uLoginTime");
                User user = new User(cid,cName,cPhone,cIdentity,cPassword,cRegisterTime,cLoginTime);
                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return list;
    }

    /**
     * 根据手机号查找用户
     *
     * @param uPhone 要查找的用户的手机号
     * @return 用户的信息
     */
    @Override
    public User findUserByUPhone(String uPhone) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_USER_BY_U_PHONE);
            preparedStatement.setString(1,uPhone);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                Integer uid = resultSet.getInt("uid");
                String uName = resultSet.getString("uName");
                String uIdentity = resultSet.getString("uIdentity");
                String uPassword = resultSet.getString("uPassword");
                Timestamp uRegisterTime = resultSet.getTimestamp("uRegisterTime");
                Timestamp uLoginTime = resultSet.getTimestamp("uLoginTime");
                User user = new User(uid,uName,uPhone,uIdentity,uPassword,uRegisterTime,uLoginTime);
                return user;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return null;
    }

    /**
     * 插入用户信息
     *
     * @param user 插入用户信息
     * @return true表示成功, false表示失败
     */
    @Override
    public boolean insert(User user) throws DuplicateIdentityException {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_INSERT);
            preparedStatement.setString(1,user.getuName());
            preparedStatement.setString(2,user.getuPhone());
            preparedStatement.setString(3,user.getuIdentity());
            preparedStatement.setString(4,user.getuPassword());
            return preparedStatement.executeUpdate()>0;
        } catch (SQLException throwables) {
            if (throwables.getMessage().endsWith("for key 'uidentity'")) {
                DuplicateIdentityException duplicateIdentityException = new DuplicateIdentityException(throwables.getMessage());
                throw duplicateIdentityException;
            } else {
                throwables.printStackTrace();
            }
        }finally {
            DruidUtil.closeConnectionAndStatement(connection,preparedStatement);
        }
        return false;
    }

    /**
     * 用户的修改
     *
     * @param uid  用户的ID
     * @param user 新的用户对象
     * @return ture表示更新成功，false表示更新失败
     */
    @Override
    public boolean update(int uid, User user) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_UPDATE);
            preparedStatement.setString(1,user.getuName());
            preparedStatement.setString(2,user.getuPhone());
            preparedStatement.setString(3,user.getuIdentity());
            preparedStatement.setString(4,user.getuPassword());
            preparedStatement.setInt(5,uid);
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.closeConnectionAndStatement(connection,preparedStatement);
        }
        return false;
    }

    /**
     * 根据ID删除用户信息
     *
     * @param uid 要删除的用户ID
     * @return true表示成功，false表示删除失败
     */
    @Override
    public boolean delete(int uid) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_DELETE);
            preparedStatement.setInt(1,uid);
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.closeConnectionAndStatement(connection,preparedStatement);
        }
        return false;
    }
}
